﻿Imports System.Data.OleDb
Imports DTO
Public Class Sach_DAO

#Region "Nhập sách"
    Public Shared Function NhapSach(ByVal sach As Sach_DTO) As Integer
        Dim sql As String = String.Format("select MaSach from Sach where TenSach = '{0}' and MaTheLoai = {1} and TacGia ='{2}'", sach.Ten, sach.MaTL, sach.TacGia)
        Dim flag As Integer
        Dim obj As Object = DataProvider.ExcuteScalar(sql)
        If (obj <> Nothing) Then
            Dim masach As Integer = Integer.Parse(obj.ToString())
            sql = String.Format("update Sach set SoLuong = SoLuong + {0},DonGia = {1} where MaSach = {2}", sach.SoLuong, sach.DonGia, masach)
            flag = DataProvider.ExcuteNonQuery(sql)
        Else
            sql = String.Format("insert into Sach(TenSach,MaTheLoai,TacGia,SoLuong,DonGia,MaTinhTrang) values ('{0}',{1},'{2}',{3},{4},1)", sach.Ten, sach.MaTL, sach.TacGia, sach.SoLuong, sach.DonGia)
            flag = DataProvider.ExcuteNonQuery(sql)
        End If
        Return flag
    End Function
#End Region

#Region "Lấy danh sách sách"
    Public Shared Function LaySach() As DataTable
        Dim sql As String = "select s.MaSach,s.TenSach,s.MaTheLoai,tl.TenTheLoai,s.TacGia,s.SoLuong,s.DonGia,s.MaTinhTrang,tt.Ten from Sach s,TheLoai tl,TinhTrangSach tt where s.MaTheLoai = tl.MaTheLoai and s.MaTinhTrang = tt.Ma"
        Dim tb As DataTable = DataProvider.ExcuteReader(sql)
        Return tb
    End Function
#End Region

#Region "Lấy sách theo mã"
    Public Shared Function LaySachTheoMa(ByVal ma As Integer) As DataTable
        Dim sql As String = String.Format("select * from Sach s,TheLoai tl where s.MaTheLoai = tl.MaTheLoai and MaSach = {0}", ma)
        Dim tb As DataTable = DataProvider.ExcuteReader(sql)
        Return tb
    End Function
#End Region

#Region "Lấy tình trạng sách"
    Public Shared Function LayTinhTrang() As DataTable
        Dim sql As String = "select * from TinhTrangSach"
        Dim tb As DataTable = DataProvider.ExcuteReader(sql)
        Return tb
    End Function
#End Region

#Region "Sửa thông tin sách"
    Public Shared Function SuaSach(ByVal ma As Integer, ByVal ten As String, ByVal matl As Integer, ByVal tg As String, ByVal sl As Integer, ByVal dg As Double, ByVal matt As Integer) As Integer
        Dim sql As String = String.Format("update Sach set TenSach = '{0}' , MaTheLoai = {1} , TacGia = '{2}' , SoLuong = {3} , DonGia = {4} , MaTinhTrang = {5} where MaSach = {6}", ten, matl, tg, sl, dg, matt, ma)
        Return DataProvider.ExcuteNonQuery(sql)
    End Function
#End Region

#Region "Tra cứu sách"
    Public Shared Function TraCuuSach(ByVal sach As Sach_CRT) As DataTable

        Dim strDKTenSach = "1=1"
        Dim strDKTacGia = "1=1"
        Dim strDKTheLoai = "1=1"

        If (sach.Ten <> "") Then
            strDKTenSach = String.Format(" s.TenSach like '%{0}%' ", sach.Ten)

        End If

        If (sach.TacGia <> "") Then
            strDKTacGia = String.Format(" s.TacGia like '%{0}%' ", sach.TacGia)

        End If


        If (sach.MaTheLoai <> 0) Then
            strDKTheLoai = String.Format(" s.MaTheLoai = {0} ", sach.MaTheLoai)

        End If

        Dim strDKWhere As String = " Where s.MaTheLoai = tl.MaTheLoai and s.MaTinhTrang = tt.Ma "

        strDKWhere += " and " + strDKTenSach
        strDKWhere += " and " + strDKTacGia
        strDKWhere += " and " + strDKTheLoai

        Dim strSQL As String = "Select s.TenSach,s.TacGia,tl.TenTheLoai,s.SoLuong,s.DonGia,tt.Ten From Sach s,TheLoai tl,TinhTrangSach tt "
        strSQL += strDKWhere
        strSQL += " Order by TenSach "

        Dim tb As DataTable = DataProvider.ExcuteReader(strSQL)
        Return tb
    End Function
#End Region

#Region "Báo cáo sách tồn"
    Public Shared Function BaoCao(ByVal thangbaocao As Integer) As DataTable
        Dim baocaotb As New DataTable()
        baocaotb.Columns.Add("TenSach")
        baocaotb.Columns.Add("TonDau")
        baocaotb.Columns.Add("SoLuongNhap")
        baocaotb.Columns.Add("SoLuongBan")
        baocaotb.Columns.Add("TonCuoi")

        Dim sql As String = "select MaSach,TenSach,SoLuong from Sach"
        Dim sachtb As New DataTable()
        sachtb = DataProvider.ExcuteReader(sql)
        For Each row As DataRow In sachtb.Rows
            Dim masach As Integer = Integer.Parse(row.Item(0).ToString())
            Dim tensach As String = row.Item(1).ToString()
            Dim toncuoi As Integer = Integer.Parse(row.Item(2).ToString())
            Dim thanghientai As Integer = Integer.Parse(Date.Today.Month.ToString())
            While thanghientai >= thangbaocao
                sql = String.Format("select sum(ctpn.SoLuong) from Sach s,PhieuNhap pn,CT_PhieuNhap ctpn where s.MaSach = ctpn.MaSach and pn.MaPhieuNhap = ctpn.MaPhieuNhap and month(pn.NgayNhap) = {0} and s.MaSach = {1}", thanghientai, masach)
                Dim soluongnhap As Integer
                Dim ojb As Object = DataProvider.ExcuteScalar(sql).ToString()
                If ojb <> Nothing Then
                    soluongnhap = Integer.Parse(ojb)
                Else
                    soluongnhap = 0
                End If

                sql = String.Format("select sum(cthd.SoLuong) from Sach s,HoaDon hd,CT_HoaDon cthd where s.MaSach = cthd.MaSach and hd.MaHoaDon = cthd.MaHoaDon and month(hd.NgayLap) = {0} and s.MaSach = {1}", thanghientai, masach)
                Dim soluongban As Integer
                ojb = DataProvider.ExcuteScalar(sql).ToString()
                If ojb <> Nothing Then
                    soluongban = Integer.Parse(ojb)
                Else
                    soluongban = 0
                End If


                Dim tondau As Integer = toncuoi + soluongban - soluongnhap

                If thanghientai = thangbaocao Then
                    Dim row1 As DataRow = baocaotb.NewRow()
                    row1("TenSach") = tensach.ToString()
                    row1("TonDau") = tondau.ToString()
                    row1("SoLuongNhap") = soluongnhap.ToString()
                    row1("SoLuongBan") = soluongban.ToString()
                    row1("TonCuoi") = toncuoi.ToString()
                    baocaotb.Rows.Add(row1)
                End If
                thanghientai = thanghientai - 1
                toncuoi = tondau
            End While
        Next
        Return baocaotb
    End Function
#End Region

End Class